"""
数据库管理模块
负责 SQLite 数据库的连接、初始化和基础操作
"""

import sqlite3
import os
from datetime import datetime
from typing import Optional, List, Dict, Any


class DatabaseManager:
    """数据库管理器类"""
    
    def __init__(self, db_path: str = "data/tasks.db"):
        """
        初始化数据库管理器
        
        Args:
            db_path: 数据库文件路径
        """
        self.db_path = db_path
        self._ensure_data_directory()
        self._init_database()
    
    def _ensure_data_directory(self):
        """确保数据目录存在"""
        data_dir = os.path.dirname(self.db_path)
        if data_dir and not os.path.exists(data_dir):
            os.makedirs(data_dir)
    
    def _init_database(self):
        """初始化数据库，创建表和索引"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            # 创建任务表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS tasks (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT NOT NULL,
                    description TEXT,
                    priority TEXT DEFAULT 'medium' CHECK (priority IN ('high', 'medium', 'low')),
                    status TEXT DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'completed')),
                    category TEXT,
                    due_date TEXT,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建索引以提高查询性能
            indexes = [
                'CREATE INDEX IF NOT EXISTS idx_status ON tasks(status)',
                'CREATE INDEX IF NOT EXISTS idx_priority ON tasks(priority)',
                'CREATE INDEX IF NOT EXISTS idx_due_date ON tasks(due_date)',
                'CREATE INDEX IF NOT EXISTS idx_category ON tasks(category)',
                'CREATE INDEX IF NOT EXISTS idx_created_at ON tasks(created_at)'
            ]
            
            for index_sql in indexes:
                cursor.execute(index_sql)
            
            conn.commit()
    
    def get_connection(self) -> sqlite3.Connection:
        """
        获取数据库连接
        
        Returns:
            SQLite 数据库连接对象
        """
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row  # 使查询结果可以通过列名访问
        return conn
    
    def execute_query(self, query: str, params: tuple = ()) -> List[sqlite3.Row]:
        """
        执行查询语句
        
        Args:
            query: SQL 查询语句
            params: 查询参数
            
        Returns:
            查询结果列表
        """
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, params)
            return cursor.fetchall()
    
    def execute_update(self, query: str, params: tuple = ()) -> int:
        """
        执行更新语句（INSERT, UPDATE, DELETE）
        
        Args:
            query: SQL 更新语句
            params: 更新参数
            
        Returns:
            受影响的行数
        """
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, params)
            conn.commit()
            return cursor.rowcount
    
    def get_last_insert_id(self) -> int:
        """
        获取最后插入记录的 ID
        
        Returns:
            最后插入记录的 ID
        """
        with self.get_connection() as conn:
            cursor = conn.cursor()
            return cursor.lastrowid
    
    def create_task(self, task_data: Dict[str, Any]) -> int:
        """
        创建新任务
        
        Args:
            task_data: 任务数据字典
            
        Returns:
            新创建任务的 ID
        """
        query = '''
            INSERT INTO tasks (title, description, priority, status, category, due_date)
            VALUES (?, ?, ?, ?, ?, ?)
        '''
        params = (
            task_data.get('title'),
            task_data.get('description'),
            task_data.get('priority', 'medium'),
            task_data.get('status', 'todo'),
            task_data.get('category'),
            task_data.get('due_date')
        )
        
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, params)
            conn.commit()
            return cursor.lastrowid
    
    def get_task(self, task_id: int) -> Optional[sqlite3.Row]:
        """
        根据 ID 获取任务
        
        Args:
            task_id: 任务 ID
            
        Returns:
            任务记录或 None
        """
        query = 'SELECT * FROM tasks WHERE id = ?'
        results = self.execute_query(query, (task_id,))
        return results[0] if results else None
    
    def update_task(self, task_id: int, updates: Dict[str, Any]) -> bool:
        """
        更新任务
        
        Args:
            task_id: 任务 ID
            updates: 要更新的字段字典
            
        Returns:
            是否更新成功
        """
        if not updates:
            return False
        
        # 构建更新语句
        set_clauses = []
        params = []
        
        for field, value in updates.items():
            if field in ['title', 'description', 'priority', 'status', 'category', 'due_date']:
                set_clauses.append(f'{field} = ?')
                params.append(value)
        
        if not set_clauses:
            return False
        
        # 添加更新时间
        set_clauses.append('updated_at = ?')
        params.append(datetime.now().isoformat())
        params.append(task_id)
        
        query = f'UPDATE tasks SET {", ".join(set_clauses)} WHERE id = ?'
        rows_affected = self.execute_update(query, tuple(params))
        
        return rows_affected > 0
    
    def delete_task(self, task_id: int) -> bool:
        """
        删除任务
        
        Args:
            task_id: 任务 ID
            
        Returns:
            是否删除成功
        """
        query = 'DELETE FROM tasks WHERE id = ?'
        rows_affected = self.execute_update(query, (task_id,))
        return rows_affected > 0
    
    def list_tasks(self, filters: Optional[Dict[str, Any]] = None) -> List[sqlite3.Row]:
        """
        获取任务列表
        
        Args:
            filters: 过滤条件字典
            
        Returns:
            任务列表
        """
        query = 'SELECT * FROM tasks'
        params = []
        
        if filters:
            conditions = []
            
            if 'status' in filters:
                conditions.append('status = ?')
                params.append(filters['status'])
            
            if 'priority' in filters:
                conditions.append('priority = ?')
                params.append(filters['priority'])
            
            if 'category' in filters:
                conditions.append('category = ?')
                params.append(filters['category'])
            
            if conditions:
                query += ' WHERE ' + ' AND '.join(conditions)
        
        query += ' ORDER BY created_at DESC'
        
        return self.execute_query(query, tuple(params))
    
    def get_task_count(self) -> int:
        """
        获取任务总数
        
        Returns:
            任务总数
        """
        query = 'SELECT COUNT(*) as count FROM tasks'
        result = self.execute_query(query)
        return result[0]['count'] if result else 0
    
    def get_task_stats(self) -> Dict[str, int]:
        """
        获取任务统计信息
        
        Returns:
            包含各种统计信息的字典
        """
        stats = {}
        
        # 按状态统计
        status_query = '''
            SELECT status, COUNT(*) as count 
            FROM tasks 
            GROUP BY status
        '''
        status_results = self.execute_query(status_query)
        for row in status_results:
            stats[f'status_{row["status"]}'] = row['count']
        
        # 按优先级统计
        priority_query = '''
            SELECT priority, COUNT(*) as count 
            FROM tasks 
            GROUP BY priority
        '''
        priority_results = self.execute_query(priority_query)
        for row in priority_results:
            stats[f'priority_{row["priority"]}'] = row['count']
        
        # 总数统计
        stats['total'] = self.get_task_count()
        
        return stats